package cn.gson.zuche.model.dao;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import cn.gson.zuche.model.GlobalParamHandler;
import cn.gson.zuche.model.bean.User;
import cn.gson.zuche.model.bean.UserYhq;
import cn.gson.zuche.model.jdbc.MySqlDb;
import cn.gson.zuche.model.jdbc.ResultHandler;

public class UserYhqDao implements ResultHandler<UserYhq> {

	private MySqlDb db = MySqlDb.getInstance();

	/**
	 * 查询用户可用优惠券
	 * 
	 * @param userId
	 * @return
	 * @throws SQLException
	 */
	public List<UserYhq> findokYhqById(Long userId) throws SQLException {
		String sql = "SELECT user_yhq_user_id,yhq_number,yhq_zt,yhq_type,yhq_sm,yhq_date "
				+ "FROM user_yhq_use,user_yhq " + "WHERE user_id=" + userId
				+ " AND yhq_zt='可使用' and yhq_number>0 and user_yhq_use.yhq_id=user_yhq.yhq_id;";
		return db.executeQuery(sql, this);
	}

	/**
	 * 查询用户过期优惠券
	 * 
	 * @param userId
	 * @return
	 * @throws SQLException
	 */
	public List<UserYhq> findnoYhqById(Long userId) throws SQLException {
		String sql = "SELECT user_yhq_user_id,yhq_number,yhq_zt,yhq_type,yhq_sm,yhq_date "
				+ "FROM user_yhq_use,user_yhq " + "WHERE user_id=" + userId
				+ " AND yhq_zt='已过期' and user_yhq_use.yhq_id=user_yhq.yhq_id;";
		return db.executeQuery(sql, this);
	}

	public List findyhqcount(Long userid) throws SQLException {
		String sql = "SELECT SUM(yhq_number) as yhq_number FROM user_yhq_use WHERE user_id = '"+userid+"' AND yhq_zt = '可使用'";
		return db.executeQuery(sql,new ResultHandler(){

			@Override
			public Object doHander(Map row) {
				// TODO Auto-generated method stub
				return row.get("yhq_number");
			}
			
		});

	}

	@Override
	public UserYhq doHander(Map<String, Object> row) {
		UserYhq u = new UserYhq();
		u.setUseryhqid(row.get("user_yhq_user_id") + "");
		u.setYhqnumber((Integer) row.get("yhq_number"));
		u.setYhqzt((String) row.get("yhq_zt"));
		u.setYhqtype((Integer) row.get("yhq_type"));
		u.setYhqsm((String) row.get("yhq_sm"));
		u.setOuttime(row.get("yhq_date"));
		return u;
	}
	/*查询用户是否有此用户券*/
	public List<UserYhq> findHasYhq(Object userId,Object yhqid) throws SQLException {
		String sql = "SELECT * FROM user_yhq_use WHERE user_id=" + userId+" and yhq_id="+yhqid;
		return db.executeQuery(sql, this);
	}
	
	/**
	 * 活动中心给用户分配增加新优惠卷
	 */
	public boolean addUseryhq(Object userid,Object qyhid) throws SQLException{
		String sql = "INSERT into user_yhq_use(user_id,yhq_id,yhq_number) values(?,?,1)";
		return db.executeUpdate(sql, new GlobalParamHandler(userid,qyhid))>0;
	}
	
	
	
	
	
	
	

	public boolean updatenum(String yhqid) throws SQLException {
		String sql = "update user_yhq_use set yhq_number='0' where user_yhq_user_id="+yhqid+"";
		return db.executeUpdate(sql)>0;
		
	}
	public List<UserYhq> findyhqmoney(String yhqid) throws SQLException {
		String sql = "SELECT yhq_type from user_yhq_use,user_yhq WHERE user_yhq_use.yhq_id=user_yhq.yhq_id AND user_yhq_user_id="+yhqid+"";
		return db.executeQuery(sql, this);
		
	}
	
}

	
